﻿using System;
using System.Collections.Generic;
using System.Text;
using Manager.App_Code.IDAL;
using Manager.App_Code.DTO;
using System.Data;
namespace Manager.App_Code.DAL
{
    class KhachHangDAL:StandardConnectDB,IKhachHang
    {
        public List<DTO.KhachHangDTO> GetlistKhachHang()
        {
            List<KhachHangDTO> listKhachHang = new List<KhachHangDTO>();
            DataTable dt = this.DTExecuteSelect("Select * from KhachHang");
            foreach (DataRow dr in dt.Rows)
            {
                listKhachHang.Add(ConvertObjectDataRowToKhachHangDTO(dr));
            }

            return listKhachHang;
            
        }

        public DTO.KhachHangDTO GetKhachHangByID(string MaKH)
        {
            KhachHangDTO khachhang = new KhachHangDTO();
            Dictionary<string, object> paramlist = new Dictionary<string, object>();

            paramlist.Add("@MaKH", MaKH);

            DataTable dt = this.DTExecuteSelect("SP_KhachHang_GetByID", paramlist);
            if (dt.Rows.Count > 0)
            {
                khachhang = ConvertObjectDataRowToKhachHangDTO(dt.Rows[0]);
                return khachhang;
            }

            return null;

            
            
        }

        public List<DTO.KhachHangDTO> GetAllKhachHangByMaQuanHuyen(string MaQuanHuyen)
        {
            List<KhachHangDTO> listKhachHang = new List<KhachHangDTO>();
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@MaQuanHuyen", MaQuanHuyen);
            DataTable dt = this.DTExecuteSelect("SP_KhachHang_GetByMaQuanHuyen",paramlist);
            foreach (DataRow dr in dt.Rows)
            {
                listKhachHang.Add(ConvertObjectDataRowToKhachHangDTO(dr));
            }

            return listKhachHang;
            
        }

        public List<DTO.KhachHangDTO> GetAllKhachHangByMaNhomKhach(string MaNhomKhach)
        {
            List<KhachHangDTO> listKhachHang = new List<KhachHangDTO>();
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@MaNhomKhach", MaNhomKhach);
            DataTable dt = this.DTExecuteSelect("SP_KhachHang_GetByMaNhomKhach", paramlist);
            foreach (DataRow dr in dt.Rows)
            {
                listKhachHang.Add(ConvertObjectDataRowToKhachHangDTO(dr));
            }

            return listKhachHang;
            
        }

        public bool InsertKhachHang(DTO.KhachHangDTO khachhang)
        {
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@MaKH", khachhang.MaKH);
            paramlist.Add("@TenKH", khachhang.TenKH);
            paramlist.Add("@DiaChi", khachhang.DiaChi);
            paramlist.Add("@DienThoai", khachhang.DienThoai);
            paramlist.Add("@SoFax", khachhang.SoFax);
            paramlist.Add("@SinhNhat", khachhang.SinhNhat);
            paramlist.Add("@Email", khachhang.Email);
            paramlist.Add("@Website", khachhang.Website);
            paramlist.Add("@Passport", khachhang.Passport);
            paramlist.Add("@TreEm", khachhang.TreEm);
            paramlist.Add("@Nu", khachhang.Nu);
            paramlist.Add("@QuocTich", khachhang.MaQuocGia.MaQuocGia); 
            paramlist.Add("@MaNhomKhach", khachhang.MaNhomKhach.MaNhomKhach);
            paramlist.Add("@GhiChu", khachhang.GhiChu);
            paramlist.Add("@NgayTao", khachhang.NgayTao);

            return ExecuteUpdate("SP_KhachHang_Insert", paramlist);
            
        }

        public bool UpdateKhachHang(DTO.KhachHangDTO khachhang)
        {
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@MaKH", khachhang.MaKH);
            paramlist.Add("@TenKH", khachhang.TenKH);
            paramlist.Add("@DiaChi", khachhang.DiaChi);
            paramlist.Add("@DienThoai", khachhang.DienThoai);
            paramlist.Add("@SoFax", khachhang.SoFax);
            paramlist.Add("@SinhNhat", khachhang.SinhNhat);
            paramlist.Add("@Email", khachhang.Email);
            paramlist.Add("@Website", khachhang.Website);
            paramlist.Add("@Passport", khachhang.Passport);
            paramlist.Add("@TreEm", khachhang.TreEm);
            paramlist.Add("@Nu", khachhang.Nu);
            paramlist.Add("@QuocTich", khachhang.MaQuocGia.MaQuocGia);
            paramlist.Add("@MaNhomKhach", khachhang.MaNhomKhach.MaNhomKhach);
            paramlist.Add("@GhiChu", khachhang.GhiChu);
            paramlist.Add("@NgayTao", khachhang.NgayTao);

            return ExecuteUpdate("SP_KhachHang_Update", paramlist);
            
        }

        public bool DeleteKhachHang(DTO.KhachHangDTO khachhang)
        {
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@MaKH", khachhang.MaKH);
            return ExecuteUpdate("SP_KhachHang_Delete", paramlist);
            
        }

        public DTO.KhachHangDTO ConvertObjectDataRowToKhachHangDTO(System.Data.DataRow dr)
        {
            KhachHangDTO khachhang = new KhachHangDTO();
            if (dr != null)
            {
                khachhang.MaKH = dr["MaKH"].ToString();
                khachhang.TenKH = dr["TenKH"].ToString();
                khachhang.DiaChi = dr["DiaChi"].ToString();
                khachhang.DienThoai = dr["DienThoai"].ToString();
                khachhang.SoFax = dr["SoFax"].ToString();
                khachhang.TreEm = bool.Parse(dr["TreEm"].ToString());
                khachhang.Nu = bool.Parse(dr["Nu"].ToString());
                khachhang.SinhNhat = DateTime.Parse(dr["SinhNhat"].ToString());
                khachhang.Email = dr["Email"].ToString();
                khachhang.Website = dr["Website"].ToString();
                khachhang.Passport = dr["Passport"].ToString();

                if (dr["QuocTich"].ToString() != "")
                {
                    IQuocGia iquocgia = new QuocGiaDAL();
                    khachhang.MaQuocGia = iquocgia.GetQuocGiaByID(dr["QuocTich"].ToString());
                }
                if (dr["MaNhomKhach"].ToString() != "")
                {
                    INhomKhach iNhomKhach = new NhomKhachDAL();
                    khachhang.MaNhomKhach = iNhomKhach.GetNhomKhachByID(dr["MaNhomKhach"].ToString());
                }

                
                if (dr["GhiChu"].ToString() != "")
                    khachhang.GhiChu = dr["GhiChu"].ToString();

                khachhang.NgayTao = DateTime.Parse(dr["NgayTao"].ToString());

                return khachhang;
            }
            else
                return null;
        }


        public int GetMaxMaKhach()
        {
            DataTable dt = this.DTExecuteSelect("SELECT MAX(STT) AS MaxMaPhieu FROM dbo.KhachHang");
            string a = dt.Rows[0]["MaxMaPhieu"].ToString();
            if (a != "")
                return int.Parse(a)+1;
            return 1;
        }


        public List<KhachHangDTO> GetAllKhachHangByNgayTao(DateTime TuNgay, DateTime DenNgay)
        {
            List<KhachHangDTO> listKhachHang = new List<KhachHangDTO>();
            Dictionary<string, object> paramlist = new Dictionary<string, object>();
            paramlist.Add("@TuNgay", TuNgay);
            paramlist.Add("@DenNgay", DenNgay);
            DataTable dt = this.DTExecuteSelect("SP_KhachHang_GetByNgayTao", paramlist);
            foreach (DataRow dr in dt.Rows)
            {
                listKhachHang.Add(ConvertObjectDataRowToKhachHangDTO(dr));
            }

            return listKhachHang;
        }
    }
}
